﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class BenhNhanDAO
    {
        #region 1. Insert

        public static bool InsertBenhNhan(BenhNhanDTO benhnhan)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", benhnhan.Ma));
                sqlParams.Add(new SqlParameter(@"HoTen",benhnhan.Hoten));
                sqlParams.Add(new SqlParameter(@"GioiTinh",benhnhan.Gioitinh));
                sqlParams.Add(new SqlParameter(@"NgaySinh",benhnhan.Ngaysinh));
                sqlParams.Add(new SqlParameter(@"DiaChi",benhnhan.Diachi));
                sqlParams.Add(new SqlParameter(@"MaQuan",benhnhan.Maquan));
                sqlParams.Add(new SqlParameter(@"DienThoai",benhnhan.Dienthoai));
                sqlParams.Add(new SqlParameter(@"MaNgheNghiep",benhnhan.Manghenghiep));
                sqlParams.Add(new SqlParameter(@"MaLoai",benhnhan.Maloai));
                sqlParams.Add(new SqlParameter(@"KhamDinhKi",benhnhan.Khamdinhki));
                sqlParams.Add(new SqlParameter(@"HinhAnh",benhnhan.Hinhanh));
                sqlParams.Add(new SqlParameter(@"ChiPhi",benhnhan.ChiPhi));
                sqlParams.Add(new SqlParameter(@"DuaTruoc",benhnhan.DuaTruoc));
                sqlParams.Add(new SqlParameter(@"DaChupPhim", benhnhan.DaChupPhim)); 
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertBenhNhan", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool InsertDotDieuTri(int mabn, DateTime ngay)
        {
            bool result = false;
            try
            {
                // Create List Sql ParameterMaBenhNhan
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"MaBenhNhan", mabn));
                sqlParams.Add(new SqlParameter(@"NgayKham", ngay));
                sqlParams.Add(new SqlParameter(@"MaBacSi", 1));
                sqlParams.Add(new SqlParameter(@"LyDoKham", ""));
                sqlParams.Add(new SqlParameter(@"ChanDoan", ""));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertDotDieuTri", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        
        public static bool InsertUpdateChiTietDieuTri(ChiTietDieuTriDTO ct)
        {
            bool result = false;
            try
            {
                // Create List Sql ParameterMaBenhNhan
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ct.Ma));
                sqlParams.Add(new SqlParameter(@"MaDotDieuTri", ct.MaDotDieuTri));
                sqlParams.Add(new SqlParameter(@"MaDichVu", ct.MaDichVu));
                sqlParams.Add(new SqlParameter(@"Ngay", ct.NgayKham));
                sqlParams.Add(new SqlParameter(@"Rang", ct.Rang));
                sqlParams.Add(new SqlParameter(@"CongViecDieuTri", ct.CongViec));
                sqlParams.Add(new SqlParameter(@"GhiChu", ct.GhiChu));
                sqlParams.Add(new SqlParameter(@"ChiPhi", ct.ChiPhi));
                sqlParams.Add(new SqlParameter(@"DuaTruoc", ct.DuaTruoc));
                sqlParams.Add(new SqlParameter(@"ConLai", ct.ConLai));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertUpdateChiTietDieuTri", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool InsertBenhNhan_Rang(int mabn, string rang, int mat, int trangthai, string thongtin)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"mabn", mabn));
                sqlParams.Add(new SqlParameter(@"rang", rang));
                sqlParams.Add(new SqlParameter(@"mat", mat));
                sqlParams.Add(new SqlParameter(@"trangthai", trangthai));
                sqlParams.Add(new SqlParameter(@"thongtin", thongtin));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertBenhNhan_Rang", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static bool InsertUpdateBenhNhan_Rang(int mabn, string rang, int mat, int trangthai, string thongtin)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"mabn", mabn));
                sqlParams.Add(new SqlParameter(@"rang", rang));
                sqlParams.Add(new SqlParameter(@"mat", mat));
                sqlParams.Add(new SqlParameter(@"tinhtrang", trangthai));
                sqlParams.Add(new SqlParameter(@"thongtin", thongtin));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertUpdateBenhNhan_Rang", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool InsertBenhNhan_BenhTienSu(int mabn, int mabts, string ghichu)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"MaBenhNhan", mabn));
                sqlParams.Add(new SqlParameter(@"MaBenhTienSu", mabts));
                sqlParams.Add(new SqlParameter(@"GhiChu", ghichu));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertBenhNhan-BenhTienSu", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static bool InsertUpdateBenhNhan_BenhTienSu(int mabn, int mabts, string ghichu)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"MaBenhNhan", mabn));
                sqlParams.Add(new SqlParameter(@"MaBenhTienSu", mabts));
                sqlParams.Add(new SqlParameter(@"GhiChu", ghichu));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertUpdateBenhNhan-BenhTienSu", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion

        #region 2. Update

        public static bool UpdateBenhNhan(BenhNhanDTO benhnhan)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", benhnhan.Ma));
                sqlParams.Add(new SqlParameter(@"HoTen", benhnhan.Hoten));
                sqlParams.Add(new SqlParameter(@"GioiTinh", benhnhan.Gioitinh));
                sqlParams.Add(new SqlParameter(@"NgaySinh", benhnhan.Ngaysinh));
                sqlParams.Add(new SqlParameter(@"DiaChi", benhnhan.Diachi));
                sqlParams.Add(new SqlParameter(@"MaQuan", benhnhan.Maquan));
                sqlParams.Add(new SqlParameter(@"DienThoai", benhnhan.Dienthoai));
                sqlParams.Add(new SqlParameter(@"MaNgheNghiep", benhnhan.Manghenghiep));
                sqlParams.Add(new SqlParameter(@"MaLoai", benhnhan.Maloai));
                sqlParams.Add(new SqlParameter(@"KhamDinhKi", benhnhan.Khamdinhki));
                sqlParams.Add(new SqlParameter(@"HinhAnh", benhnhan.Hinhanh));
                sqlParams.Add(new SqlParameter(@"ChiPhi", benhnhan.ChiPhi));
                sqlParams.Add(new SqlParameter(@"DuaTruoc", benhnhan.DuaTruoc));
                sqlParams.Add(new SqlParameter(@"DaChupPhim", benhnhan.DaChupPhim)); 
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdateBenhNhan", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool UpdateDotDieuTri(DieuTriDTO dt)
        {
            bool result = false;
            try
            {
                // Create List Sql ParameterMaBenhNhan
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", dt.Ma));
                sqlParams.Add(new SqlParameter(@"MaBenhNhan", dt.MaBenhNhan));
                sqlParams.Add(new SqlParameter(@"NgayKham", dt.NgayKham));
                sqlParams.Add(new SqlParameter(@"MaBacSi", 1));
                sqlParams.Add(new SqlParameter(@"LyDoKham", dt.LyDoKham));
                sqlParams.Add(new SqlParameter(@"ChanDoan", dt.ChanDoan));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdateDotDieuTri", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion

        #region 3. Delete

        public static bool DeleteBenhNhan(BenhNhanDTO benhnhan)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", benhnhan.Ma));
                            
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteBenhNhan", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool DeleteDotDieuTri(int ma)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ma));

                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteDotDieuTri", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool DeleteChiTietDieuTri(int maddt, int ma)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ma));
                sqlParams.Add(new SqlParameter(@"MaDotDieuTri", maddt));                

                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteChiTietDieuTri", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool DeleteBenhNhan_BenhTienSu(int mabn, int mabts)
            {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"MaBenhNhan", mabn));
                sqlParams.Add(new SqlParameter(@"MaBenhTienSu", mabts));                

                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteBenhNhan-BenhTienSu", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region 4. Retrive

        public static List<BenhNhanDTO> SelectBenhNhanByAll()
        {
            List<BenhNhanDTO> list = new List<BenhNhanDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectBenhNhansAll", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    BenhNhanDTO benhnhan = new BenhNhanDTO();
                    benhnhan.Ma = int.Parse(dr["Ma"].ToString());
                    benhnhan.Hoten = dr["HoTen"].ToString();
                    benhnhan.Gioitinh = int.Parse(dr["GioiTinh"].ToString());
                    if(dr["NgaySinh"].ToString() != "")
                        benhnhan.Ngaysinh = DateTime.Parse(dr["NgaySinh"].ToString());
                    benhnhan.Diachi = dr["DiaChi"].ToString();
                    benhnhan.Maquan = int.Parse(dr["MaQuan"].ToString());
                    benhnhan.Dienthoai = dr["DienThoai"].ToString();
                    if (benhnhan.Dienthoai.Length > 12)
                        benhnhan.Dienthoai = benhnhan.Dienthoai.Remove(12);
                    benhnhan.Manghenghiep = int.Parse(dr["MaNgheNghiep"].ToString());
                    benhnhan.Maloai = int.Parse(dr["MaLoai"].ToString());
                    benhnhan.Khamdinhki = int.Parse(dr["KhamDinhKi"].ToString());
                    benhnhan.Hinhanh = dr["HinhAnh"].ToString();
                    benhnhan.ChiPhi = double.Parse(dr["ChiPhi"].ToString());
                    benhnhan.DuaTruoc = double.Parse(dr["DuaTruoc"].ToString());
                    benhnhan.ConNo = benhnhan.ChiPhi - benhnhan.DuaTruoc;
                    benhnhan.DaChupPhim = int.Parse(dr["DaChupPhim"].ToString());
                    list.Add(benhnhan);
                }  
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<BenhNhanDTO> SelectBenhNhanByHoTen(string hoten)
        {
            List<BenhNhanDTO> list = new List<BenhNhanDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@HoTen", hoten));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectBenhNhanByHoTen", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    BenhNhanDTO benhnhan = new BenhNhanDTO();
                    benhnhan.Ma = int.Parse(dr["Ma"].ToString());
                    benhnhan.Hoten = dr["HoTen"].ToString();
                    benhnhan.Gioitinh = int.Parse(dr["GioiTinh"].ToString());
                    if (dr["NgaySinh"].ToString() != "")
                        benhnhan.Ngaysinh = DateTime.Parse(dr["NgaySinh"].ToString());
                    benhnhan.Diachi = dr["DiaChi"].ToString();
                    benhnhan.Maquan = int.Parse(dr["MaQuan"].ToString());
                    benhnhan.Dienthoai = dr["DienThoai"].ToString();
                    if (benhnhan.Dienthoai.Length > 12)
                        benhnhan.Dienthoai = benhnhan.Dienthoai.Remove(12);
                    benhnhan.Manghenghiep = int.Parse(dr["MaNgheNghiep"].ToString());
                    benhnhan.Maloai = int.Parse(dr["MaLoai"].ToString());
                    benhnhan.Khamdinhki = int.Parse(dr["KhamDinhKi"].ToString());
                    benhnhan.Hinhanh = dr["HinhAnh"].ToString();
                    benhnhan.ChiPhi = double.Parse(dr["ChiPhi"].ToString());
                    benhnhan.DuaTruoc = double.Parse(dr["DuaTruoc"].ToString());
                    benhnhan.ConNo = benhnhan.ChiPhi - benhnhan.DuaTruoc;
                    benhnhan.DaChupPhim = int.Parse(dr["DaChupPhim"].ToString());
                    list.Add(benhnhan);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<BenhNhanDTO> SelectBenhNhanByThongTin(BenhNhanDTO bn)
        {
            List<BenhNhanDTO> list = new List<BenhNhanDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@Ma", bn.Ma));
                sqlParams.Add(new SqlParameter("@HoTen", bn.Hoten));
                sqlParams.Add(new SqlParameter("@Gioitinh", bn.Gioitinh));
                sqlParams.Add(new SqlParameter("@Ngaysinh", bn.Ngaysinh));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectBenhNhanByThongTin", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    BenhNhanDTO benhnhan = new BenhNhanDTO();
                    benhnhan.Ma = int.Parse(dr["Ma"].ToString());
                    benhnhan.Hoten = dr["HoTen"].ToString();
                    benhnhan.Gioitinh = int.Parse(dr["GioiTinh"].ToString());
                    if (dr["NgaySinh"].ToString() != "")
                        benhnhan.Ngaysinh = DateTime.Parse(dr["NgaySinh"].ToString());
                    benhnhan.Diachi = dr["DiaChi"].ToString();
                    benhnhan.Maquan = int.Parse(dr["MaQuan"].ToString());
                    benhnhan.Dienthoai = dr["DienThoai"].ToString();
                    if (benhnhan.Dienthoai.Length > 12)
                        benhnhan.Dienthoai = benhnhan.Dienthoai.Remove(12);
                    benhnhan.Manghenghiep = int.Parse(dr["MaNgheNghiep"].ToString());
                    benhnhan.Maloai = int.Parse(dr["MaLoai"].ToString());
                    benhnhan.Khamdinhki = int.Parse(dr["KhamDinhKi"].ToString());
                    benhnhan.Hinhanh = dr["HinhAnh"].ToString();
                    benhnhan.ChiPhi = double.Parse(dr["ChiPhi"].ToString());
                    benhnhan.DuaTruoc = double.Parse(dr["DuaTruoc"].ToString());
                    benhnhan.ConNo = benhnhan.ChiPhi - benhnhan.DuaTruoc;
                    benhnhan.DaChupPhim = int.Parse(dr["DaChupPhim"].ToString());
                    list.Add(benhnhan);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static DataTable SelectChiTietDieuTriByDotDieuTri(int maddt)
        {
            DataTable dt = new DataTable();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();                
                sqlParams.Add(new SqlParameter(@"MaDDT", maddt));
                dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectChiTietDieuTriByDotDieuTri", sqlParams);                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataTable SelectChiTietDieuTriByBenhNhan(int mabn)
        {
            DataTable dt = new DataTable();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"mabn", mabn));
                dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectAllChiTietDieuTriByBenhNhan", sqlParams);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static int GetMaxMa()
        {
            DataTable dt = new DataTable();
            try
            {
                // Create List Sql Parameter                
                dt = SqlDataAccessHelper.ExecuteQuery("usp_GetMaBNMax");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (!dt.Rows[0]["Max"].Equals(System.DBNull.Value))
                return int.Parse(dt.Rows[0]["Max"].ToString()) + 1;
            else
                return 1;
        }

        public static BenhNhanDTO SelectBenhNhanByMa(int ma)
        {
            BenhNhanDTO benhnhan = new BenhNhanDTO();
            try
            {
                
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ma));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectBenhNhan", sqlParams);
                DataRow dr = dt.Rows[0];
                benhnhan.Ma = int.Parse(dr["Ma"].ToString());
                benhnhan.Hoten = dr["HoTen"].ToString();
                benhnhan.Gioitinh = int.Parse(dr["GioiTinh"].ToString());
                if (dr["NgaySinh"].ToString() != "")
                    benhnhan.Ngaysinh = DateTime.Parse(dr["NgaySinh"].ToString());
                benhnhan.Diachi = dr["DiaChi"].ToString();
                benhnhan.Maquan = int.Parse(dr["MaQuan"].ToString());
                benhnhan.Dienthoai = dr["DienThoai"].ToString();
                benhnhan.Manghenghiep = int.Parse(dr["MaNgheNghiep"].ToString());
                benhnhan.Maloai = int.Parse(dr["MaLoai"].ToString());
                benhnhan.Khamdinhki = int.Parse(dr["KhamDinhKi"].ToString());
                benhnhan.Hinhanh = dr["HinhAnh"].ToString();
                benhnhan.ChiPhi = double.Parse(dr["ChiPhi"].ToString());
                benhnhan.DuaTruoc = double.Parse(dr["DuaTruoc"].ToString());
                benhnhan.ConNo = benhnhan.ChiPhi - benhnhan.DuaTruoc;
                benhnhan.DaChupPhim = int.Parse(dr["DaChupPhim"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return benhnhan;
        }

        public static DieuTriDTO SelectDotDieuTriByMa(int ma)
        {
            DieuTriDTO ddt = new DieuTriDTO();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ma));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectDotDieuTri", sqlParams);
                DataRow dr = dt.Rows[0];                
                ddt.Ma = int.Parse(dr["Ma"].ToString());
                ddt.MaBenhNhan = int.Parse(dr["MaBenhNhan"].ToString());
                ddt.NgayKham = DateTime.Parse(dr["NgayKham"].ToString());                
                ddt.MaBacSi = int.Parse(dr["MaBacSi"].ToString());
                ddt.LyDoKham = dr["LyDoKham"].ToString();                
                ddt.ChanDoan = dr["ChanDoan"].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ddt;
        }

        public static ChiTietDieuTriDTO SelectChiTietDieuTriByMa(int ma, int maddt)
        {
            ChiTietDieuTriDTO ctdt = new ChiTietDieuTriDTO();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", ma));
                sqlParams.Add(new SqlParameter(@"MaDotDieuTri", maddt));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectChiTietDieuTri", sqlParams);
                DataRow dr = dt.Rows[0];
                ctdt.Ma = int.Parse(dr["Ma"].ToString());
                ctdt.MaDotDieuTri = int.Parse(dr["MaDotDieuTri"].ToString());
                ctdt.NgayKham = DateTime.Parse(dr["Ngay"].ToString());
                ctdt.MaDichVu = int.Parse(dr["MaDichVu"].ToString());
                ctdt.ChiPhi = double.Parse(dr["ChiPhi"].ToString());
                ctdt.DuaTruoc = double.Parse(dr["DuaTruoc"].ToString());
                ctdt.ConLai = double.Parse(dr["ConLai"].ToString());
                ctdt.GhiChu = dr["GhiChu"].ToString();
                ctdt.Rang = dr["Rang"].ToString();
                ctdt.CongViec = dr["CongViecDieuTri"].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ctdt;
        }

        public static List<RangDTO> SelectAllTTRByMaBN(int mabn)
        {
            List<RangDTO> dsrang = new List<RangDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@Ma", mabn));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectRangByMaBenhNhan", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    RangDTO rang = new RangDTO();
                    rang.MaBN = int.Parse(dr["MaBN"].ToString());
                    rang.Rang = dr["Rang"].ToString();
                    rang.MatRang = int.Parse(dr["Mat"].ToString());
                    rang.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
                    rang.ThongTin = dr["ThongTin"].ToString();
                    dsrang.Add(rang);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dsrang;
        }        

        #endregion
    }
}
